package com.edu.admin.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * 
 */
public class ExcelUtils {

	/**
	 * 功能: Excel数据导入到数据库 参数: originUrl[Excel表的所在路径] 参数: startRow[从第几行开始] 参数:
	 * endRow[到第几行结束 (0表示所有行; 正数表示到第几行结束; 负数表示到倒数第几行结束)] 参数:
	 * attrs[对应实体类中要导入的属性名]
	 */
	public static List<Map<String, Object>> importExcel(String originUrl, int startRow, int endRow, String[] attrs)
			throws IOException {
		// 是否打印提示信息
		boolean showInfo = true;
		return doImportExcel(originUrl, startRow, endRow, showInfo, attrs);
	}

	/**
	 * 功能:真正实现导入
	 */
	private static List<Map<String, Object>> doImportExcel(String originUrl, int startRow, int endRow, boolean showInfo,
			String[] attrs) throws IOException {
		// 判断文件是否存在
		File file = new File(originUrl);
		if (!file.exists()) {
			throw new IOException("文件名为" + file.getName() + "Excel文件不存在！");
		}
		String fileName = file.getName();
		Workbook wb = null;
		FileInputStream fis = null;
		List<Row> rowList = new ArrayList<>();
		ArrayList<String> cellList = new ArrayList<String>();
		try {
			fis = new FileInputStream(file);
			// 去读Excel
			if (fileName.endsWith("xlsx")) {
				wb = new XSSFWorkbook(fis);// Excel 2007
			} else if (fileName.endsWith("xls")) {
				wb = new HSSFWorkbook(fis);// Excel 2003

			}
			Sheet sheet = wb.getSheetAt(0);
			// 获取最后行号
			int lastRowNum = sheet.getLastRowNum();
			if (lastRowNum > 0) { // 如果>0，表示有数据
				out("\n开始读取名为【" + sheet.getSheetName() + "】的内容：", showInfo);
			}
			Row row = null;
			// 循环读取
			for (int i = startRow; i <= lastRowNum + endRow; i++) {
				row = sheet.getRow(i);
				if (row != null) {

					rowList.add(row);
					out("第" + (i + 1) + "行：", showInfo, false);

					if (i == 0) {
						for (int j = 0; j < row.getLastCellNum(); j++) {
							if (row.getCell(j) != null) {
								cellList.add(getCellValue(row.getCell(j)));
							}
						}
						out(cellList.toString(), showInfo);
					}

					// 获取每一单元格的值
					for (int j = 0; j < row.getLastCellNum(); j++) {
						String value = getCellValue(row.getCell(j));
						if (!value.equals("")) {
							out(value + " | ", showInfo, false);
						}
					}
					out("", showInfo);
				}
			}
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			wb = null;
			fis.close();
		}
		String[] attrs2 = {};
		attrs2 = cellList.toArray(attrs2);
		attrs2 = Arrays.copyOfRange(attrs2, 0, attrs.length);
		return returnObjectList(rowList, attrs, attrs2);
	}

	/**
	 * 功能:获取单元格的值
	 */
	private static String getCellValue(Cell cell) {
		Object result = "";
		if (cell != null) {
			switch (cell.getCellType()) {
			case Cell.CELL_TYPE_STRING:
				result = cell.getStringCellValue();
				break;
			case Cell.CELL_TYPE_NUMERIC:
				//result = cell.getNumericCellValue();
				DecimalFormat df = new DecimalFormat("0");
				result = df.format(cell.getNumericCellValue());
				break;
			case Cell.CELL_TYPE_BOOLEAN:
				result = cell.getBooleanCellValue();
				break;
			case Cell.CELL_TYPE_FORMULA:
				result = cell.getCellFormula();
				break;
			case Cell.CELL_TYPE_ERROR:
				result = cell.getErrorCellValue();
				break;
			case Cell.CELL_TYPE_BLANK:
				break;
			default:
				break;
			}
		}
		return result.toString();
	}

	/**
	 * 功能:返回指定的对象集合
	 */
	private static List<Map<String, Object>> returnObjectList(List<Row> rowList, String[] attrs1, String[] attrs2) {
		List<Map<String, Object>> objectList = null;
		try {
			objectList = new ArrayList<>();
			int j = 0;
			for (Row row : rowList) {

				Map<String, Object> map = new HashMap<>();
				if (j == 0) {
					map.put("cellNum", attrs2.length);
					map.put("rowNum", rowList.size());
				}
				for (int k = 0; k < attrs2.length; k++) {
					String value = getCellValue(row.getCell(k));
					if (j == 0) {
						map.put(attrs1[k], value);
					} else {
						map.put(attrs2[k], value);
					}
				}
				j++;
				objectList.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return objectList;
	}

	/**
	 * 功能:输出提示信息(普通信息打印)
	 */
	private static void out(String info, boolean showInfo) {
		if (showInfo) {
			System.out.print(info + (showInfo ? "\n" : ""));
		}
	}

	/**
	 * 功能:输出提示信息(同一行的不同单元格信息打印)
	 */
	private static void out(String info, boolean showInfo, boolean nextLine) {
		if (showInfo) {
			if (nextLine) {
				System.out.print(info + (showInfo ? "\n" : ""));
			} else {
				System.out.print(info);
			}
		}
	}

}
